﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Common;
using Automobile.service.Model;
using System.Data;
using System.Data.SqlClient;
namespace Automobile.service.DAL
{
  public class ProductDAL
    {
      SQLHelper helper = new SQLHelper();
      /// <summary>
      /// 商品列表显示
      /// </summary>
      /// <returns></returns>
      public List<Product> Product_show(int PageSize ,int PageIndex)
      {
          string sql = "select Product_Id, Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Name,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark from(select ROW_NUMBER()over(order by Product_Id)as   nid,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Name,Product_Barcode,Product_frim,Product.Product_Type_Id,Product_Id,Product_Unit_Id,Product_Remark from Product INNER JOIN  Product_Type on Product.Product_Type_Id=Product_Type.Product_Type_Id )as temp where nid between (" + PageIndex + "-1)*" + PageSize + "+1 and " + PageIndex + "*" + PageSize + "";
          DataTable table = helper.ExecDataTable(sql);
          List<Product> list = new List<Product>();
          foreach (DataRow item in table.Rows)
          {
              Product P = new Product();
              P.Product_Id = Convert.ToInt32(item["Product_Id"]);
              P.Product_Barcode = item["Product_Barcode"].ToString();
              P.Product_Bprid = Convert.ToDecimal(item["Product_Bprid"]);
              P.Product_frim = item["Product_frim"].ToString();
              P.Product_Modelspe = item["Product_Modelspe"].ToString();
              P.Product_Name = item["Product_Name"].ToString();
              P.Product_Price = Convert.ToDecimal(item["Product_Price"]);
              P.Product_Remark = item["Product_Remark"].ToString();
              P.Product_Type_Name = item["Product_Type_Name"].ToString();
              P.Product_Unit_Id = item["Product_Unit_Id"].ToString();
              list.Add(P);
          }
          return list;
      }
     
      /// <summary>
      /// 删除供应商
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public int Del(int id)
      {
          string sql = "delete Product where Product_Id=" + id + "";
          return helper.ExecNonquery(sql);
      }
      /// <summary>
      /// 根据Id的获取数据
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public DataTable Product_Hu(int id)
      {
          string sql = "select Product_Id,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark,Product_Type_Id from Product where Product_Id=" + id + "";
          return helper.ExecDataTable(sql);

      }
      /// <summary>
      /// 修改数据
      /// </summary>
      /// <param name="s"></param>
      /// <returns></returns>
      public int UptProduct(Product s)
      {
          string ProcName = "UptProduct";
          SqlParameter[] par = new SqlParameter[]
            {
                            new SqlParameter("@Product_Id",s.Product_Id),
                            new SqlParameter("@Product_Name",s.Product_Name),
                            new SqlParameter("@Product_Modelspe",s.Product_Modelspe),
                            new SqlParameter("@Product_Bprid",s.Product_Bprid),
                            new SqlParameter("@Product_Price",s.Product_Price),
                            new SqlParameter("@Product_Type_Id",s.Product_Type_Id),
                            new SqlParameter("@Product_Barcode",s.Product_Barcode),
                            new SqlParameter("@Product_frim",s.Product_frim),
                            new SqlParameter("@Product_Unit_Id",s.Product_Unit_Id),
                            new SqlParameter("@Product_Remark",s.Product_Remark),
                };
          return helper.ExecNonquery_Proc(ProcName, par);
      }
      /// <summary>
      /// 添加数据
      /// </summary>
      /// <param name="s"></param>
      /// <returns></returns>
      public int AddProduct(Product s)
      {
          string ProcName = "AddSProduct";
          SqlParameter[] par = new SqlParameter[]
            {
                            new SqlParameter("@Product_Name",s.Product_Name),
                            new SqlParameter("@Product_Modelspe",s.Product_Modelspe),
                            new SqlParameter("@Product_Bprid",s.Product_Bprid),
                            new SqlParameter("@Product_Price",s.Product_Price),
                            new SqlParameter("@Product_Type_Id",s.Product_Type_Id),
                            new SqlParameter("@Product_Barcode",s.Product_Barcode),
                            new SqlParameter("@Product_frim",s.Product_frim),
                            new SqlParameter("@Product_Unit_Id",s.Product_Unit_Id),
                            new SqlParameter("@Product_Remark",s.Product_Remark),
                };
          return helper.ExecNonquery_Proc(ProcName, par);
      }
      /// <summary>
      /// 类型显示
      /// </summary>
      /// <returns></returns>
      public DataTable Product_Type_Show()
      {
          string sql = "select Product_Type_Id,Product_Type_Name from Product_Type";
          return helper.ExecDataTable(sql);
      }
      /// <summary>
      /// 商品总数量
      /// </summary>
      /// <returns></returns>
      public int Product_Count(string Product_Name, string Product_Modelspe, int? Product_Type_Id)
      {
          string sql = "select COUNT(*) from Product where 1=1";
          if (!string.IsNullOrEmpty(Product_Name))
          {
              sql += "and Product_Name like '%" + Product_Name + "%'";
          }
          if (!string.IsNullOrEmpty(Product_Modelspe))
          {
              sql += "and Product_Modelspe='" + Product_Modelspe + "'";
          }
          if (Product_Type_Id != 0)
          {
              sql += "and Product.Product_Type_Id=" + Product_Type_Id + "";
          }
          return Convert.ToInt32(helper.ExecScalar(sql));
      }
      /// <summary>
      /// 商品列表显示查询
      /// </summary>
      /// <returns></returns>
      public List<Product> Product_show_Sel(int PageSize, int PageIndex,string Product_Name,string Product_Modelspe,int? Product_Type_Id)
      {
          string sql = "select Product_Id, Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Name,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark from(select ROW_NUMBER()over(order by Product_Id)as   nid,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Name,Product_Barcode,Product_frim,Product.Product_Type_Id,Product_Id,Product_Unit_Id,Product_Remark from Product INNER JOIN  Product_Type on Product.Product_Type_Id=Product_Type.Product_Type_Id where 1=1";
          if (!string.IsNullOrEmpty(Product_Name))
          {
              sql += "and Product_Name like '%" + Product_Name + "%'";
          }
          if (!string.IsNullOrEmpty(Product_Modelspe))
          {
              sql += "and Product_Modelspe='" + Product_Modelspe + "'";
          }
          if (Product_Type_Id!=0)
          {
              sql += "and Product.Product_Type_Id=" + Product_Type_Id + "";
          }
          sql += " )as temp where nid between (" + PageIndex + "-1)*" + PageSize + "+1 and " + PageIndex + "*" + PageSize + "";
          DataTable table = helper.ExecDataTable(sql);
          List<Product> list = new List<Product>();
          foreach (DataRow item in table.Rows)
          {
              Product P = new Product();
              P.Product_Id = Convert.ToInt32(item["Product_Id"]);
              P.Product_Barcode = item["Product_Barcode"].ToString();
              P.Product_Bprid = Convert.ToDecimal(item["Product_Bprid"]);
              P.Product_frim = item["Product_frim"].ToString();
              P.Product_Modelspe = item["Product_Modelspe"].ToString();
              P.Product_Name = item["Product_Name"].ToString();
              P.Product_Price = Convert.ToDecimal(item["Product_Price"]);
              P.Product_Remark = item["Product_Remark"].ToString();
              P.Product_Type_Name = item["Product_Type_Name"].ToString();
              P.Product_Unit_Id = item["Product_Unit_Id"].ToString();
              list.Add(P);
          }

          return list;
      }
          public static object _lock = new object();
          public static int count = 1;
          public string GetRandom1()
          {
              lock (_lock)
              {
                  if (count >= 10000)
                  {
                      count = 1;
                  }
                  var number = "P" + DateTime.Now.ToString("yyMMddHHmmss") + count.ToString("0000");
                  count++;
                  return number;
              }
          }
          public string GetRandom2()
          {
              lock (_lock)
              {
                  return "T" + DateTime.Now.Ticks;

              }
          }

          public string GetRandom3()
          {
              lock (_lock)
              {
                  Random ran = new Random();
                  return "U" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ran.Next(1000, 9999).ToString();
              }
          }
      }
    }

